import sys
import os
from PyQt5.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, 
                            QTableView, QMenuBar, QMenu, QAction, QFileDialog, QStatusBar,
                            QToolBar, QMessageBox, QInputDialog, QFontDialog, QColorDialog)
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QFont, QColor, QIcon
from PyQt5.QtCore import Qt, QSize
import openpyxl
import xlrd
from xlwt import Workbook

class ExcelEditor(QMainWindow):
    def __init__(self):
        super().__init__()
        self.current_file = None
        self.initUI()
        
    def initUI(self):
        # 设置窗口
        self.setWindowTitle("Excel编辑器")
        self.setGeometry(100, 100, 1200, 800)
        
        # 创建中心部件和布局
        central_widget = QWidget()
        self.setCentralWidget(central_widget)
        self.layout = QVBoxLayout(central_widget)
        
        # 创建表格视图
        self.table_view = QTableView()
        self.model = QStandardItemModel(self)
        self.table_view.setModel(self.model)
        self.layout.addWidget(self.table_view)
        
        # 设置表格属性
        self.table_view.horizontalHeader().setStretchLastSection(True)
        self.table_view.verticalHeader().setVisible(True)
        self.table_view.setAlternatingRowColors(True)
        
        # 创建菜单栏
        self.create_menu_bar()
        
        # 创建工具栏
        self.create_tool_bar()
        
        # 创建状态栏
        self.statusBar = QStatusBar()
        self.setStatusBar(self.statusBar)
        self.statusBar.showMessage("就绪")
        
        # 显示窗口
        self.show()
    
    def create_menu_bar(self):
        menubar = self.menuBar()
        
        # 文件菜单
        file_menu = menubar.addMenu('文件')
        
        # 新建动作
        new_action = QAction('新建', self)
        new_action.setShortcut('Ctrl+N')
        new_action.triggered.connect(self.new_file)
        file_menu.addAction(new_action)
        
        # 打开动作
        open_action = QAction('打开', self)
        open_action.setShortcut('Ctrl+O')
        open_action.triggered.connect(self.open_file)
        file_menu.addAction(open_action)
        
        # 保存动作
        save_action = QAction('保存', self)
        save_action.setShortcut('Ctrl+S')
        save_action.triggered.connect(self.save_file)
        file_menu.addAction(save_action)
        
        # 另存为动作
        save_as_action = QAction('另存为', self)
        save_as_action.setShortcut('Ctrl+Shift+S')
        save_as_action.triggered.connect(self.save_as_file)
        file_menu.addAction(save_as_action)
        
        file_menu.addSeparator()
        
        # 退出动作
        exit_action = QAction('退出', self)
        exit_action.setShortcut('Ctrl+Q')
        exit_action.triggered.connect(self.close)
        file_menu.addAction(exit_action)
        
        # 编辑菜单
        edit_menu = menubar.addMenu('编辑')
        
        # 剪切动作
        cut_action = QAction('剪切', self)
        cut_action.setShortcut('Ctrl+X')
        cut_action.triggered.connect(self.cut)
        edit_menu.addAction(cut_action)
        
        # 复制动作
        copy_action = QAction('复制', self)
        copy_action.setShortcut('Ctrl+C')
        copy_action.triggered.connect(self.copy)
        edit_menu.addAction(copy_action)
        
        # 粘贴动作
        paste_action = QAction('粘贴', self)
        paste_action.setShortcut('Ctrl+V')
        paste_action.triggered.connect(self.paste)
        edit_menu.addAction(paste_action)
        
        edit_menu.addSeparator()
        
        # 撤销动作
        undo_action = QAction('撤销', self)
        undo_action.setShortcut('Ctrl+Z')
        undo_action.triggered.connect(self.undo)
        edit_menu.addAction(undo_action)
        
        # 重做动作
        redo_action = QAction('重做', self)
        redo_action.setShortcut('Ctrl+Y')
        redo_action.triggered.connect(self.redo)
        edit_menu.addAction(redo_action)
        
        # 格式菜单
        format_menu = menubar.addMenu('格式')
        
        # 字体动作
        font_action = QAction('字体', self)
        font_action.triggered.connect(self.set_font)
        format_menu.addAction(font_action)
        
        # 颜色动作
        color_action = QAction('颜色', self)
        color_action.triggered.connect(self.set_color)
        format_menu.addAction(color_action)
    
    def create_tool_bar(self):
        toolbar = QToolBar('工具栏', self)
        toolbar.setIconSize(QSize(16, 16))
        self.addToolBar(toolbar)
        
        # 添加工具栏动作
        new_action = QAction('新建', self)
        new_action.triggered.connect(self.new_file)
        toolbar.addAction(new_action)
        
        open_action = QAction('打开', self)
        open_action.triggered.connect(self.open_file)
        toolbar.addAction(open_action)
        
        save_action = QAction('保存', self)
        save_action.triggered.connect(self.save_file)
        toolbar.addAction(save_action)
        
        toolbar.addSeparator()
        
        cut_action = QAction('剪切', self)
        cut_action.triggered.connect(self.cut)
        toolbar.addAction(cut_action)
        
        copy_action = QAction('复制', self)
        copy_action.triggered.connect(self.copy)
        toolbar.addAction(copy_action)
        
        paste_action = QAction('粘贴', self)
        paste_action.triggered.connect(self.paste)
        toolbar.addAction(paste_action)
    
    def new_file(self):
        self.model.clear()
        self.current_file = None
        self.setWindowTitle("Excel编辑器 - 未命名")
        self.statusBar.showMessage("已新建空白表格")
    
    def open_file(self):
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getOpenFileName(
            self, "打开Excel文件", "", "Excel Files (*.xls *.xlsx);;All Files (*)", options=options
        )
        
        if file_path:
            self.current_file = file_path
            self.setWindowTitle(f"Excel编辑器 - {os.path.basename(file_path)}")
            
            # 根据文件扩展名选择不同的读取方法
            if file_path.endswith('.xlsx'):
                self.read_xlsx(file_path)
            elif file_path.endswith('.xls'):
                self.read_xls(file_path)
            
            self.statusBar.showMessage(f"已打开文件: {os.path.basename(file_path)}")
    
    def read_xlsx(self, file_path):
        try:
            workbook = openpyxl.load_workbook(file_path)
            sheet = workbook.active
            
            # 设置表格行数和列数
            self.model.setRowCount(sheet.max_row)
            self.model.setColumnCount(sheet.max_column)
            
            # 读取数据并填充表格
            for row in range(sheet.max_row):
                for col in range(sheet.max_column):
                    cell_value = sheet.cell(row=row+1, column=col+1).value
                    item = QStandardItem(str(cell_value) if cell_value is not None else "")
                    self.model.setItem(row, col, item)
            
            # 设置表头
            for col in range(sheet.max_column):
                self.model.setHeaderData(col, Qt.Horizontal, chr(65 + col))
                
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法读取XLSX文件: {str(e)}")
    
    def read_xls(self, file_path):
        try:
            workbook = xlrd.open_workbook(file_path)
            sheet = workbook.sheet_by_index(0)
            
            # 设置表格行数和列数
            self.model.setRowCount(sheet.nrows)
            self.model.setColumnCount(sheet.ncols)
            
            # 读取数据并填充表格
            for row in range(sheet.nrows):
                for col in range(sheet.ncols):
                    cell_value = sheet.cell_value(row, col)
                    item = QStandardItem(str(cell_value) if cell_value is not None else "")
                    self.model.setItem(row, col, item)
            
            # 设置表头
            for col in range(sheet.ncols):
                self.model.setHeaderData(col, Qt.Horizontal, chr(65 + col))
                
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法读取XLS文件: {str(e)}")
    
    def save_file(self):
        if not self.current_file:
            self.save_as_file()
        else:
            if self.current_file.endswith('.xlsx'):
                self.write_xlsx(self.current_file)
            elif self.current_file.endswith('.xls'):
                self.write_xls(self.current_file)
            
            self.statusBar.showMessage(f"已保存文件: {os.path.basename(self.current_file)}")
    
    def save_as_file(self):
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getSaveFileName(
            self, "保存Excel文件", "", "Excel Files (*.xlsx);;Excel 97-2003 Files (*.xls);;All Files (*)", options=options
        )
        
        if file_path:
            self.current_file = file_path
            self.setWindowTitle(f"Excel编辑器 - {os.path.basename(file_path)}")
            
            if file_path.endswith('.xlsx'):
                self.write_xlsx(file_path)
            else:
                if not file_path.endswith('.xls'):
                    file_path += '.xls'
                    self.current_file = file_path
                self.write_xls(file_path)
            
            self.statusBar.showMessage(f"已保存文件: {os.path.basename(file_path)}")
    
    def write_xlsx(self, file_path):
        try:
            workbook = openpyxl.Workbook()
            sheet = workbook.active
            
            # 写入数据
            for row in range(self.model.rowCount()):
                for col in range(self.model.columnCount()):
                    item = self.model.item(row, col)
                    if item:
                        sheet.cell(row=row+1, column=col+1).value = item.text()
            
            workbook.save(file_path)
            
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法写入XLSX文件: {str(e)}")
    
    def write_xls(self, file_path):
        try:
            workbook = Workbook()
            sheet = workbook.add_sheet('Sheet1')
            
            # 写入数据
            for row in range(self.model.rowCount()):
                for col in range(self.model.columnCount()):
                    item = self.model.item(row, col)
                    if item:
                        sheet.write(row, col, item.text())
            
            workbook.save(file_path)
            
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法写入XLS文件: {str(e)}")
    
    def cut(self):
        self.copy()
        selected = self.table_view.selectedIndexes()
        for index in selected:
            self.model.setItem(index.row(), index.column(), QStandardItem(""))
    
    def copy(self):
        selected = self.table_view.selectedIndexes()
        if not selected:
            return
            
        # 按行和列排序
        selected.sort(key=lambda x: (x.row(), x.column()))
        rows = [index.row() for index in selected]
        cols = [index.column() for index in selected]
        
        min_row, max_row = min(rows), max(rows)
        min_col, max_col = min(cols), max(cols)
        
        # 构建数据矩阵
        data = []
        for row in range(min_row, max_row + 1):
            row_data = []
            for col in range(min_col, max_col + 1):
                item = self.model.item(row, col)
                row_data.append(item.text() if item else "")
            data.append("\t".join(row_data))
        
        # 复制到剪贴板
        QApplication.clipboard().setText("\n".join(data))
    
    def paste(self):
        clipboard = QApplication.clipboard()
        text = clipboard.text()
        if not text:
            return
            
        rows = text.split("\n")
        start_row, start_col = 0, 0
        
        # 获取选中的第一个单元格
        selected = self.table_view.selectedIndexes()
        if selected:
            start_row = selected[0].row()
            start_col = selected[0].column()
        
        # 粘贴数据
        for i, row in enumerate(rows):
            cells = row.split("\t")
            for j, cell in enumerate(cells):
                current_row = start_row + i
                current_col = start_col + j
                
                # 如果超出当前模型大小，扩展模型
                if current_row >= self.model.rowCount():
                    self.model.setRowCount(current_row + 1)
                if current_col >= self.model.columnCount():
                    self.model.setColumnCount(current_col + 1)
                
                self.model.setItem(current_row, current_col, QStandardItem(cell))
    
    def undo(self):
        # 简单的撤销功能实现，实际应用中可能需要更复杂的历史记录管理
        self.statusBar.showMessage("撤销功能尚未实现")
    
    def redo(self):
        # 简单的重做功能实现，实际应用中可能需要更复杂的历史记录管理
        self.statusBar.showMessage("重做功能尚未实现")
    
    def set_font(self):
        selected = self.table_view.selectedIndexes()
        if not selected:
            return
            
        font, ok = QFontDialog.getFont()
        if ok:
            for index in selected:
                item = self.model.item(index.row(), index.column())
                if item:
                    item.setFont(font)
                else:
                    new_item = QStandardItem("")
                    new_item.setFont(font)
                    self.model.setItem(index.row(), index.column(), new_item)
    
    def set_color(self):
        selected = self.table_view.selectedIndexes()
        if not selected:
            return
            
        color = QColorDialog.getColor()
        if color.isValid():
            for index in selected:
                item = self.model.item(index.row(), index.column())
                if item:
                    item.setForeground(color)
                else:
                    new_item = QStandardItem("")
                    new_item.setForeground(color)
                    self.model.setItem(index.row(), index.column(), new_item)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    
    # 设置全局样式，使界面更美观
    app.setStyle("Fusion")
    palette = app.palette()
    palette.setColor(palette.Window, QColor(240, 240, 240))
    palette.setColor(palette.WindowText, QColor(0, 0, 0))
    app.setPalette(palette)
    
    ex = ExcelEditor()
    sys.exit(app.exec_())